#include "mytable.h"
#include <qDebug>
#include <QRegularExpression>

MyTable::MyTable() {}

MyTable::MyTable(QString tableName, QString& header, QList<Course> &courseList, QMap<QString, Student> &students, GradeLevel gradeLevel)
{
    this->tableName = tableName;
    this->header = header;
    this->courseList = courseList;
    this->studentList = students.values();
    this->gradeLevel = gradeLevel;
}

bool MyTable::saveTable()
{
    QXlsx::Document xlsx;
    saveHeader(xlsx);
    saveTitle(xlsx);
    sortByTotalScore();
    saveScore(xlsx);
    checkCharityColors(xlsx);
    checkConductColors(xlsx);
    checkCourseColors(xlsx);

    if (xlsx.saveAs(this->tableName+".xlsx")) {
        return true;
    } else {
        return false;
    }
}

void MyTable::saveHeader(QXlsx::Document &xlsx)
{
    QXlsx::Format format;
    format.setFontName("宋体");
    format.setFontBold(true);
    format.setFontSize(12);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setBorderStyle(QXlsx::Format::BorderThin);

    xlsx.write(1, 1, this->header);
    xlsx.mergeCells(QXlsx::CellRange(1, 1, 2, this->courseList.length()+9), format);
    xlsx.setRowHeight(1, 13.50);
    xlsx.setRowHeight(2, 13.50);
}

void MyTable::saveTitle(QXlsx::Document &xlsx)
{
    QXlsx::Format format;
    format.setFontName("宋体");
    format.setFontSize(10);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setPatternBackgroundColor(QColor(191, 191, 191));
    format.setTextWrap(true);
    format.setBorderStyle(QXlsx::Format::BorderThin);

    xlsx.write(3, 1, "学号", format);
    xlsx.write(3, 2, "姓名", format);
    xlsx.setColumnWidth(1, 15.7);
    xlsx.setColumnWidth(2, 10.7);

    int courseLength = this->courseList.length();
    for (int i = 0; i < courseLength; i++) {
        QString course = this->courseList[i].getName()
                         +"，"+this->courseList[i].getXZ_str()
                         +"，"+this->courseList[i].getSX_str()
                         +"，学分 "+QString::number(this->courseList[i].getXF())
                         +"，学时 "+QString::number(this->courseList[i].getXS())
                         +"，"+this->courseList[i].getTime();
        xlsx.write(3, i+3, course, format);
        xlsx.setColumnWidth(i+3, 10.7);
    }
    xlsx.write(3, courseLength+3, "课程成绩", format);
    xlsx.write(3, courseLength+4, "公益分", format);
    xlsx.write(3, courseLength+5, "公益分成绩", format);
    xlsx.write(3, courseLength+6, "操行分", format);
    xlsx.write(3, courseLength+7, "操行分成绩", format);
    xlsx.write(3, courseLength+8, "奖学金成绩", format);
    xlsx.write(3, courseLength+9, "排名", format);
    for (int i = 0; i < 6; i++) {
        xlsx.setColumnWidth(i+courseLength+3, 13.7);
    }
    xlsx.setColumnWidth(courseLength+9, 7.7);
    xlsx.setRowHeight(3, 100);
}

void MyTable::saveScore(QXlsx::Document &xlsx)
{
    QXlsx::Format format;
    format.setFontName("宋体");
    format.setFontSize(10);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setBorderStyle(QXlsx::Format::BorderThin);

    int studentLength = this->studentList.length();
    int courseLength = this->courseList.length();


    for (int row = 0; row < studentLength; row++) {
        xlsx.write(row+4, 1, this->studentList[row].getXh(), format);
        xlsx.write(row+4, 2, this->studentList[row].getName(), format);

        // 保存课程分数
        for (int col = 0; col < courseLength; col++) {
            int score = this->studentList[row].getCourseScore(this->courseList[col].getId());
            if (score != -1) {
                xlsx.write(row+4, col+3, score, format);
            } else {
                xlsx.write(row+4, col+3, QVariant(), format);
            }
        }

        // 计算课程成绩
        QString courseGradeFormula = createCourseGradeFormula(row);
        xlsx.write(row+4, courseLength+3, courseGradeFormula, format);

        // 保存公益分
        xlsx.write(row+4, courseLength+4, this->studentList[row].getCharityScore(), format);
        QString charityGradeFormula = createCharityGradeFormula(row, courseLength+4);
        xlsx.write(row+4, courseLength+5, charityGradeFormula, format);


        // 保存操行分
        xlsx.write(row+4, courseLength+6, this->studentList[row].getConductScore(), format);
        QString conductGradeFormula = createConductGradeFormula(row, courseLength+6);
        xlsx.write(row+4, courseLength+7, conductGradeFormula, format);

        // 保存奖学金成绩
        QString scholarshipGradeFormula = createsScholarshipGradeFormula(row, this->sportsIndex, courseLength+3, courseLength+5, courseLength+7);
        xlsx.write(row+4, courseLength+8, scholarshipGradeFormula, format);

        // 保存排名
        xlsx.write(row+4, courseLength+9, row + 1, format);
    }
}

void MyTable::checkCharityColors(QXlsx::Document &xlsx)
{
    int charityIndex = this->courseList.length()+4;
    int rowLast = this->studentList.length()+4;
    int colLast = this->courseList.length()+10;

    for (int i = 4; i < rowLast; i++) {
        QVariant gyf = xlsx.read(i, charityIndex);
        if (gyf.toInt() < 300) {
            std::shared_ptr<QXlsx::Cell> cell;
            for (int j = 1; j < colLast; j++) {
                cell = xlsx.cellAt(i, j);
                QXlsx::Format cellFormat = cell->format();
                cellFormat.setPatternBackgroundColor(QColor(255, 0, 0));
                xlsx.write(i, j, xlsx.read(i, j), cellFormat);
            }
        }
    }
}

void MyTable::checkConductColors(QXlsx::Document &xlsx)
{
    int conductIndex = this->courseList.length()+6;
    int rowLast = this->studentList.length()+4;
    int colLast = this->courseList.length()+10;

    for (int i = 4; i < rowLast; i++) {
        QVariant cxf = xlsx.read(i, conductIndex);
        if (cxf.toDouble() < 85) {
            std::shared_ptr<QXlsx::Cell> cell;
            for (int j = 1; j < colLast; j++) {
                cell = xlsx.cellAt(i, j);
                QXlsx::Format cellFormat = cell->format();
                cellFormat.setPatternBackgroundColor(QColor(255, 0, 0));
                xlsx.write(i, j, xlsx.read(i, j), cellFormat);
            }
        }
    }
}

void MyTable::checkCourseColors(QXlsx::Document &xlsx)
{
    int courseLast = this->courseList.length()+3;
    int rowLast = this->studentList.length()+4;
    int colLast = this->courseList.length()+10;

    for (int row = 4; row < rowLast; row++) {
        for (int col = 3; col < courseLast; col++) {
            QVariant cj = xlsx.read(row, col);
            if (cj.toString().isEmpty()) continue;
            if (cj.toDouble() < 60) {
                std::shared_ptr<QXlsx::Cell> cell;
                for (int i = 1; i < colLast; i++) {
                    cell = xlsx.cellAt(row, i);
                    QXlsx::Format cellFormat = cell->format();
                    cellFormat.setPatternBackgroundColor(QColor(255, 0, 0));
                    xlsx.write(row, i, xlsx.read(row, i), cellFormat);
                }
                break;
            }
        }
    }


}

void MyTable::sortByTotalScore()
{
    std::sort(studentList.begin(), studentList.end(), [](Student& a, Student& b) {
        return a.getJxj() > b.getJxj(); // 降序排序
    });
}

QString MyTable::createCourseGradeFormula(int studentIndex)
{
    QString result = "=(";
    QString xf = "(";        // 学分
    int courseLength = this->courseList.length();

    for (int col = 0; col < courseLength; col++) {
        QRegularExpression re("^体育");
        if (!re.match(this->courseList[col].getName()).hasMatch()) {
            if (this->studentList[studentIndex].getCourseScore(this->courseList[col].getId()) != -1) {
                // 不是体育成绩
                double x = this->courseList[col].getXF();
                result += convertToExcelStyle(studentIndex+4, col+3)+"*"+QString::number(x)+"+";
                xf += QString::number(x)+"+";
            }
        } else {
            this->sportsIndex = col+3;
        }
    }

    if (result.length() > 2) {
        // 去掉最后的 +
        result = result.left(result.length() - 1);
        xf = xf.left(xf.length() - 1);
    } else {
        return "";
    }

    result += ")/"+xf+")";
    return result;
}

QString MyTable::createCharityGradeFormula(int studentIndex, int charityIndex)
{
    QString str = convertToExcelStyle(studentIndex+4, charityIndex);
    return "=IF("+str+">=300,IF("+str+"<=1000,(60+("+str+"-300)*40/700)*0.09,(60+(1000-300)*40/700)*0.09),"+str+"*0.2*0.09)";
}

QString MyTable::createConductGradeFormula(int studentIndex, int conductIndex)
{
    return "="+convertToExcelStyle(studentIndex+4, conductIndex)+"*0.11";
}

QString MyTable::createsScholarshipGradeFormula(int studentIndex, int sportsIndex, int courseGradeIndex, int charityGradeIndex, int conductGradeIndex)
{
    switch (this->gradeLevel) {
    case GradeLevel::One:
        return "="+convertToExcelStyle(studentIndex+4, courseGradeIndex)+"*0.7+"
               +convertToExcelStyle(studentIndex+4, sportsIndex)+"*0.1+"
               +convertToExcelStyle(studentIndex+4, conductGradeIndex)+"+"
               +convertToExcelStyle(studentIndex+4, charityGradeIndex);
        break;
    case GradeLevel::Two:
        return "="+convertToExcelStyle(studentIndex+4, courseGradeIndex)+"*0.7+"
               +convertToExcelStyle(studentIndex+4, sportsIndex)+"*0.1+"
               +convertToExcelStyle(studentIndex+4, conductGradeIndex)+"+"
               +convertToExcelStyle(studentIndex+4, charityGradeIndex);
        break;
    case GradeLevel::Three:
        return "="+convertToExcelStyle(studentIndex+4, courseGradeIndex)+"*0.8+"
               +convertToExcelStyle(studentIndex+4, conductGradeIndex)+"+"
               +convertToExcelStyle(studentIndex+4, charityGradeIndex);
        break;
    case GradeLevel::Four:
        return "="+convertToExcelStyle(studentIndex+4, courseGradeIndex)+"*0.89+"
               +convertToExcelStyle(studentIndex+4, conductGradeIndex);
        break;
    default:
        return "";
        break;
    }
}

QString MyTable::convertToExcelStyle(int row, int col) {
    // 将列号转换为字母
    QString colLetter;
    while (col > 0) {
        int remainder = (col - 1) % 26; // 从1开始，A对应0
        colLetter = QChar('A' + remainder) + colLetter;
        col = (col - 1) / 26;
    }

    // 将行号转换为字符串
    QString rowNumber = QString::number(row);

    // 返回结果
    return colLetter + rowNumber;
}
